﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace SE {
    public class MyObject {
        /// <summary>
        ///  Birçok Tablodan veri almak
        /// </summary>
        /// <param name="dt">DataTable param</param>
        /// <param name="classSet">Gelecek verilerin Class Tipleri</param>
        /// <param name="whereType">Kriter tipi</param>
        /// <param name="whereField">Kriter field i</param>
        /// <param name="whereParam">Kriter Values i</param>
        /// 
        private static void Join(DataTable dt, Type[] classSet, Type whereType, string whereField, string whereParam) {
            try {

                string command = "select ";
                foreach (Type t in classSet) {
                    command += t.Name + ".*, ";
                }
                command = command.Remove(command.Length - 2, 2);
                command += " where ";
                command += whereType.Name + "." + whereField + "='" + whereParam + "'";
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand(command, cnn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            } catch {

            }
        }
        /// <summary>
        /// 2 Table Join Void()
        /// </summary>
        /// <param name="dt">Datatable param</param>
        /// <param name="class1">Table1</param>
        /// <param name="class2">Table2</param>
        /// <param name="compareField1">compare 1</param>
        /// <param name="compareField2">compare 2</param>
        /// <param name="whereType">Criteria Type</param>
        /// <param name="whereTypeField">Criteria Field</param>
        /// <param name="whereParam">Criteria param</param>
        /// <param name="type">Join Type</param>
        private static void Join(DataTable dt, Type class1, Type class2, string compareField1, string compareField2, Type whereType, string whereTypeField, string whereParam, JoinType type) {
            try {
                string command = "select ";
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                command += class1.Name + ".*, ";
                command += class2.Name + ".*";
                command += " from " + class1.Name + "  " + type.ToString().Replace('_', ' ') + " " + class2.Name + " on ";
                command += class1.Name + "." + compareField1 + "=" + class2.Name + "." + compareField2 + " where ";
                command += whereType.Name + "." + whereTypeField + "='" + whereParam + "'";

                cmd.CommandText = command;
                cmd.Connection = cnn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            } catch {

            }
        }

        /// <summary>
        /// Manuel olarak yazılan sql sorgusunu DataTable olarak geri döndürür
        /// </summary>
        /// <param name="selectParam">sql select</param>
        /// <returns>DataTable</returns>
        private static void CreateSelectSp(string spQueryValue, string spName, params SqlParameter[] prms) {
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cnn;
            StringBuilder sb = new StringBuilder();
            sb.Append("create proc sp_select_" + spName + "");
            sb.AppendLine();
            if (prms!=null) {
                foreach (SqlParameter sp in prms) {
                    sb.Append(sp.ParameterName + "  " + GetDbType(sp.DbType.ToString()) + ",");
                } 
            }
            sb.Remove(sb.ToString().Length - 1, 1);
            sb.AppendLine();
            sb.Append("as");
            sb.AppendLine();
            sb.Append(spQueryValue);
            cmd.CommandText = sb.ToString();
            cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();
        }
        private static DataTable CallSelectSp(string spName, params SqlParameter[] prm) {
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            SqlCommand cmd = new SqlCommand();
            try {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnn;
                cmd.CommandText = "sp_select_" + spName;
                if (prm != null) {
                    foreach (SqlParameter pr in prm) {
                        cmd.Parameters.Add(pr);
                    }
                }
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;

            } catch {
                cnn.Close();
                return null;

            }
        }
        public static DataTable Select(string selectParam, string spName,DevelopmentType devType, params SqlParameter[] sqlParams) {
            DataTable dt;
            if (devType==DevelopmentType.Off) {
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand(selectParam, cnn);
                dt = new DataTable();
                if (sqlParams!=null) {
                    foreach (SqlParameter prm in sqlParams) {
                        cmd.Parameters.Add(prm);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            } else {
                dt = CallSelectSp(spName, sqlParams);
                if (dt == null) {
                    try {
                        CreateSelectSp(selectParam, spName, sqlParams);
                        dt = CallSelectSp(spName, sqlParams);
                    } catch (Exception ex) {
                        throw new Exception("Hata Oluştu :" + ex.Message, null);
                    }

                } 
            }
            return dt;
        }
        /// <summary>
        /// Manual olarak yazılan T-Sql sorgusu aldığı 2. parametredeki tip türünden bir generic list olarak geri döndürür.
        /// </summary>
        /// <param name="selectParam">selectString</param>
        /// <param name="returnType">Generic List</param>
        /// <returns></returns>
        public static List<object> SelectOblect(string selectParam, Type returnType) {
            List<object> MainObje = new List<object>();
            try {
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand(selectParam, cnn);
                DataTable dt = new DataTable(); 
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                foreach (DataRow dr in dt.Rows) {
                    object obj = Activator.CreateInstance(returnType, true);
                    foreach (DataColumn dc in dt.Columns) {
                        object objVal = dr[dc];
                        bool ss = dr[dc].Equals(DBNull.Value);
                        if (ss) objVal = null;
                        obj.GetType().GetProperty(dc.ColumnName).SetValue(obj, objVal, null);
                    }
                    MainObje.Add(obj);
                }
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu :" + ex.Message, null);
            }
            return MainObje;
        }
        /// <summary>
        /// Oluşturduğunuz classlara karşılık gelecek şekilde veritabanında tablolar oluşturur.
        /// </summary>
        /// <param name="type">ClassSet</param>
        public static void CreateNew(params Type[] type) {
            try {
                foreach (Type tp in type) {
                    int i = 0;
                    string[] tmp = tp.ToString().Split('.');
                    PropertyInfo[] pr = tp.GetProperties();
                    string prm = string.Empty;
                    string s = "CREATE TABLE [dbo].[" + tmp[tmp.Length - 1] + "]([ID] [int] IDENTITY(1,1) NOT NULL,";
                    foreach (PropertyInfo p in pr) {
                        object k = tp.GetProperty(p.Name).Name;
                        if (p.Name != "ID") {
                            if (CheckType(tp.GetProperty(p.Name).PropertyType.FullName)) {
                                s += "[" + p.Name + "] " + GetDbType(tp.GetProperty(p.Name).PropertyType.FullName) + " NULL,";
                            }
                            i++;
                        }
                    }
                    s += "CONSTRAINT [PK_" + tmp[tmp.Length - 1] + "] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]";
                    SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                    SqlCommand cmd = new SqlCommand(s, cnn);
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                    cnn.Close();
                    CreateProcedures(tp, tp.Name);
                }
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu   :;" + ex.Message, null);
            }
        }
        /// <summary>
        /// Bir yada daha fazla parametre girilen class ı alır. Gönderilen class ın diğer propertylerini doldurur.
        /// </summary>
        /// <param name="myObjectClass">Class </param>
        /// <returns></returns>
        public static void FillObject(object myObjectClass) {
            try {
                string[] tmp = myObjectClass.ToString().Split('.');
                string tableName = tmp[tmp.Length - 1];
                PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
                object id = myObjectClass.GetType().GetProperty("ID").GetValue(myObjectClass, null);
                foreach (PropertyInfo p in pr) {
                    if (id != null) {
                        object tt = myObjectClass.GetType().GetProperty(p.Name).PropertyType.FullName;
                        if (CheckType(tt.ToString())) {
                            myObjectClass.GetType().GetProperty(p.Name).SetValue(myObjectClass, getFieldValue(tableName, p.Name, id.ToString()), null);
                        }
                    }
                }
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message, null);
            }

        }
        private static void cleanObject(object obj) {
            obj = null;
        }
        internal void FillMSIL(object obj) {
        }
        internal void ConvertMSIL(object obj) {
        }
        private static string GetDbType(string typeString) {
            string s = "[varchar](150)";
            if (typeString.Length > 40) {
                string[] str = typeString.Split('[');
                string[] str1 = str[2].Split(',');
                string es = str1[0];
                string[] lst = es.Split('.');
                es = lst[1];
                switch (es) {
                    case "String":
                        s = "Varchar(150)";
                        break;
                    case "Int32":
                        s = "[int]"; break;
                    case "Int16":
                        s = "[smallint]"; break;
                    case "Int64":
                        s = "[bigint]"; break;
                    case "Long":
                        s = "[bigint]"; break;
                    case "Boolean":
                        s = "[bit]"; break;
                    case "DateTime":
                        s = "[datetime]"; break;
                    case "Decimal":
                        s = "[decimal]"; break;
                    case "Double":
                        s = "Money"; break;
                    case "Object":
                        s = "Varchar(MAX)"; break;
                    case "Byte":
                        s = "[bit]"; break;
                    case "Char":
                        s = "nchar(10)"; break;
                    case "Guid":
                        s = "uniqueidentifier"; break;
                }

            } else {
                string[] r = typeString.Split('.');
                if (r.Length > 1) {
                    typeString = r[1];
                    string[] v = typeString.Split(' ');
                    typeString = v[0];
                } else {
                    r = typeString.Split(' ');
                    typeString = r[0];

                }
                switch (typeString) {
                    case "String":
                        s = "Varchar(50)";
                        break;
                    case "Int32":
                        s = "[int]"; break;
                    case "Int16":
                        s = "[int]"; break;
                    case "Int64":
                        s = "[bigint]"; break;
                    case "Long":
                        s = "[bigint]"; break;
                    case "Boolean":
                        s = "[bit]"; break;
                    case "DateTime":
                        s = "[datetime]"; break;
                    case "Decimal":
                        s = "[decimal]"; break;
                    case "Double":
                        s = "Money"; break;
                    case "Object":
                        s = "Varchar(MAX)"; break;
                    case "Byte":
                        s = "[bit]"; break;
                    case "Char":
                        s = "nchar(10)"; break;
                    case "Guid":
                        s = "uniqueidentifier"; break;
                }

            }
            return s;
        }
        /// <summary>
        /// Gönderilen class propertylerine göre veritabanında benzer kayıt olup olmadığını sorgular
        /// </summary>
        /// <param name="myObjectClass">Class</param>
        /// <returns>Bool</returns>
        public static bool IsSaved(object myObjectClass) {
            try {

                bool b = false;
                string s = GetScalarValue(generateCountQuery(myObjectClass)).ToString();
                if (s != "0") {
                    b = true;
                }
                return b;
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message, null);
            }
        }
        /// <summary>
        /// Gönderilen class daki id parametresine sahip kaydı gönderilen valuelerle değiştirir.
        /// </summary>
        /// <param name="myObjectClass">Class</param>
        public static void Update(object myObjectClass) {
            PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
            try {
                string[] tmp = myObjectClass.ToString().Split('.');
                string tableName = tmp[tmp.Length - 1];
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnn;
                cmd.CommandText = "sp_" + tableName + "_Update";
                foreach (PropertyInfo p in pr) {
                    object prm = p.GetValue(myObjectClass, null);
                    if (prm == null) {
                        prm = DBNull.Value;
                    }
                    cmd.Parameters.AddWithValue("@" + p.Name, prm);
                }
                cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message, null);
            }
        }
        /// <summary>
        /// Gönderilen parametrelere göre veriyi siler.
        /// </summary>
        /// <param name="myObjectClass"></param>
        public static void Delete(object myObjectClass) {
            PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
            try {
                string[] tmp = myObjectClass.ToString().Split('.');
                string tableName = tmp[tmp.Length - 1];
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnn;
                cmd.CommandText = "sp_" + tableName + "_Delete";
                foreach (PropertyInfo p in pr) {
                    if (p.Name == "ID") {
                        object prm = p.GetValue(myObjectClass, null);
                        if (prm == null) {
                            return;
                        }
                        cmd.Parameters.AddWithValue("@" + p.Name, prm); break;
                    }
                }
                cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message, null);
            }

        }
        /// <summary>
        /// Gönderilen parametrelere göre yeni bir kayıt oluşturur.
        /// </summary>
        /// <param name="myObjectClass"></param>
        public static void Save(object myObjectClass) {
            PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
            try {
                string[] tmp = myObjectClass.ToString().Split('.');
                string tableName = tmp[tmp.Length - 1];
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = cnn;
                cmd.CommandText = "sp_" + tableName + "_Insert";
                foreach (PropertyInfo p in pr) {
                    if (p.Name != "ID") {
                        object prm = p.GetValue(myObjectClass, null);
                        if (prm == null) {
                            prm = DBNull.Value;
                        }
                        cmd.Parameters.AddWithValue("@" + p.Name, prm);
                    }
                }
                cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message, null);
            }
        }
        #region Private Methods
        private static void delete(object myObjectClass) {
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            SqlCommand cmd = new SqlCommand();

            string[] tmp = myObjectClass.ToString().Split('.');
            string t = tmp[tmp.Length - 1];
            string s = "delete from " + t + " where ";
            PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
            int i = 1;
            foreach (PropertyInfo p in pr) {
                i++;
                object o = myObjectClass.GetType().GetProperty(p.Name).GetValue(myObjectClass, null);
                if (o != null) {
                    object tt = myObjectClass.GetType().GetProperty(p.Name).PropertyType.FullName;
                    if (CheckType(tt.ToString())) {
                        i++;
                        s += " " + p.Name + "='" + o.ToString() + "' and ";
                        cmd.Parameters.AddWithValue("@" + p.Name + i.ToString(), p.Name);
                    }
                }
            }
            if (s.EndsWith("where ")) {
                s = s.Remove(s.Length - 6, 6);
            } else {
                s = s.Remove(s.Length - 4, 4);
            }
            cmd.CommandText = s;
            cmd.Connection = cnn;
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();

        }
        internal static bool CheckType(string typeString) {
            bool b = false;
            if (typeString.Length > 40) {
                string[] str = typeString.Split('[');
                string[] str1 = str[2].Split(',');
                string s = str1[0];
                string[] lst = s.Split('.');
                s = lst[1];

                switch (s) {
                    case "String":
                        b = true; break;
                    case "Int32":
                        b = true; break;
                    case "Int16":
                        b = true; break;
                    case "Int64":
                        b = true; break;
                    case "Boolean":
                        b = true; break;
                    case "DateTime":
                        b = true; break;
                    case "Decimal":
                        b = true; break;
                    case "Double":
                        b = true; break;
                    case "Long":
                        b = true; break;
                    case "Object":
                        b = true; break;
                    case "Byte":
                        b = true; break;
                    case "Char":
                        b = true; break;
                    case "Guid":
                        b = true; break;
                }
            } else {
                string[] r = typeString.Split('.');
                if (r.Length > 1) {
                    typeString = r[1];
                    string[] v = typeString.Split(' ');
                    typeString = v[0];
                } else {
                    r = typeString.Split(' ');
                    typeString = r[0];

                }
                switch (typeString) {
                    case "String":
                        b = true; break;
                    case "Int32":
                        b = true; break;
                    case "Int16":
                        b = true; break;
                    case "Int64":
                        b = true; break;
                    case "Boolean":
                        b = true; break;
                    case "DateTime":
                        b = true; break;
                    case "Decimal":
                        b = true; break;
                    case "Double":
                        b = true; break;
                    case "Long":
                        b = true; break;
                    case "Object":
                        b = true; break;
                    case "Byte":
                        b = true; break;
                    case "Char":
                        b = true; break;
                    case "Guid":
                        b = true; break;
                }

            }
            return b;

        }
        private static string generateQuery(object obj) {
            string sss = obj.ToString();
            string[] tmp = obj.ToString().Split('.');
            string t = tmp[tmp.Length - 1];
            string s = "select * from " + t + " where ";
            PropertyInfo[] pr = obj.GetType().GetProperties();
            foreach (PropertyInfo p in pr) {
                object o = obj.GetType().GetProperty(p.Name).GetValue(obj, null);
                if (o != null) {
                    object tt = obj.GetType().GetProperty(p.Name).PropertyType.FullName;
                    if (CheckType(tt.ToString())) {
                        s += " " + p.Name + "='" + o.ToString() + "' and ";
                    }
                }
            }
            if (s.EndsWith("where ")) {
                s = s.Remove(s.Length - 6, 6);
            } else {
                s = s.Remove(s.Length - 4, 4);
            }
            return s;
        }
        private static object GetScalarValue(string Query) {
            object returnValue = "";
            try {
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand(Query, cnn);
                cnn.Open();
                returnValue = cmd.ExecuteScalar();
                cnn.Close();
                if (returnValue == DBNull.Value) {
                    returnValue = null;
                }
            } catch {
            }

            return returnValue;
        }
        private static string generateOneFieldQuery(object obj, string fieldName) {
            string[] tmp = obj.ToString().Split('.');
            string t = tmp[tmp.Length - 1];
            string s = "select " + fieldName + " from " + t + " where ";
            PropertyInfo[] pr = obj.GetType().GetProperties();
            foreach (PropertyInfo p in pr) {
                object o = obj.GetType().GetProperty(p.Name).GetValue(obj, null);
                if (o != null) {
                    s += " " + p.Name + "='" + o.ToString() + "' and ";
                }
            }
            if (s.EndsWith("where ")) {
                s = s.Remove(s.Length - 6, 6);
            } else {
                s = s.Remove(s.Length - 4, 4);
            }
            return s;
        }
        private static string generateCountQuery(object obj) {
            string[] tmp = obj.ToString().Split('.');
            string t = tmp[tmp.Length - 1];
            string s = "select Count(*)  from " + t + " where ";
            PropertyInfo[] pr = obj.GetType().GetProperties();
            foreach (PropertyInfo p in pr) {
                object o = obj.GetType().GetProperty(p.Name).GetValue(obj, null);
                if (o != null) {

                    object tt = obj.GetType().GetProperty(p.Name).PropertyType.FullName;
                    if (CheckType(tt.ToString())) {
                        s += " " + p.Name + "='" + o.ToString() + "' and ";
                    }
                }
            }
            if (s.EndsWith("where ")) {
                s = s.Remove(s.Length - 6, 6);
            } else {
                s = s.Remove(s.Length - 4, 4);
            }
            return s;

        }
        private static object getFieldValue(string tableName, string field, string whereValue) {
            string query = "select " + field + " from " + tableName + " where ID='" + whereValue + "'";
            object s = GetScalarValue(query);
            return s;
        }
        private static void CreateDB(string databaseName, params Type[] TypeSet) {
            string query = "use[master] create database " + databaseName + "";
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            SqlCommand cmd = new SqlCommand(query, cnn);
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
            foreach (Type ob in TypeSet) {
                string[] ss = ob.ToString().Split('.');
                query = "use[" + databaseName + "]  Create Table [dbo].[" + ss[ss.Length - 1] + "] ( [ID] [int] IDENTITY(1,1) NOT NULL, ";
                PropertyInfo[] pr = ob.GetProperties();
                foreach (PropertyInfo p in pr) {
                    if (p.Name == "ID") {
                        continue;
                    }
                    query += "[" + p.Name + "] " + GetDbType(p.ToString()) + " NULL,";
                }
                query += " CONSTRAINT [PK_" + ss[ss.Length - 1] + "] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] ";
                cmd = new SqlCommand(query, cnn);
                cnn.Open();
                cmd.ExecuteNonQuery();
                cnn.Close();
            }
            cnn.Dispose();
        }
        #endregion

        /// <summary>
        /// Tipi verilen Class a ait tüm verileri gönderilen class tipinden bir generic list olarak geri döndürür.
        /// </summary>
        /// <param name="paramType">sample: typeof(class)</param>
        /// <returns>Generic List</returns>
        public static List<object> GetObjectsList(Type paramType) {
            List<object> MainObje = new List<object>();
            string[] prm = paramType.ToString().Split('.');
            string k = "select * from " + prm[prm.Length - 1];
            DataTable dt = Select(k, prm[prm.Length - 1], DevelopmentType.On,null);
            foreach (DataRow dr in dt.Rows) {
                object bb = Activator.CreateInstance(paramType, true);
                foreach (DataColumn dc in dt.Columns) {
                    object d = dr[dc];
                    bool ss = dr[dc].Equals(DBNull.Value);
                    if (ss) {
                        d = null;
                    }
                    bb.GetType().GetProperty(dc.ColumnName).SetValue(bb, d, null);
                }
                MainObje.Add(bb);
            }
            return MainObje;
        }
        /// <summary>
        /// Gönderilen class daki property valuelerine göre,gönderilen class tipinden bir generic list döndürür.
        /// </summary>
        /// <param name="paramClass">Class</param>
        /// <returns>GenericList</returns> 
        public static List<object> GetObjectsList(object paramClass) {
            string sss = paramClass.ToString();
            string[] tmp = paramClass.ToString().Split('.');
            string t = tmp[tmp.Length - 1];

            List<object> MainObje = new List<object>();
            string k = generateQuery(paramClass);
            DataTable dt = Select(k, t,DevelopmentType.On, null);
            foreach (DataRow dr in dt.Rows) {
                object bb = Activator.CreateInstance(paramClass.GetType(), true);
                foreach (DataColumn dc in dt.Columns) {
                    object d = dr[dc];
                    bool ss = dr[dc].Equals(DBNull.Value);
                    if (ss) {
                        d = null;
                    }
                    bb.GetType().GetProperty(dc.ColumnName).SetValue(bb, d, null);
                }
                MainObje.Add(bb);
            }
            return MainObje;
        }
        /// <summary>
        /// Gönderilen class daki parametrelere göre geri bir DataTable nesnesi döndürür.
        /// </summary>
        /// <param name="myObjectClass">Class</param>
        public static DataTable GetObjects(object myObjectClass) {
            try {
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                string command = "select ";
                DataTable dtble = new DataTable();
                try {
                    PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
                    object[] dizi = new string[pr.Length];
                    int i = 0;
                    foreach (PropertyInfo p in pr) {

                        object tt = myObjectClass.GetType().GetProperty(p.Name).PropertyType.FullName;
                        if (CheckType(tt.ToString())) {
                            dizi[i] = p.Name;
                            command += p.Name + ", ";
                            i++;
                        }
                    }
                    i = 0;
                    command = command.Remove(command.Length - 2, 1);
                    string[] tmp = myObjectClass.ToString().Split('.');
                    command += " from " + tmp[tmp.Length - 1] + " where";
                    foreach (string s in dizi) {
                        if (s != null) {
                            object m = myObjectClass.GetType().GetProperty(s).GetValue(myObjectClass, null);
                            if (m != null) {

                                object tt = myObjectClass.GetType().GetProperty(s).PropertyType.FullName;
                                if (CheckType(tt.ToString())) {
                                    i++;
                                    command += " " + s + "=@" + i.ToString() + " and ";
                                    cmd.Parameters.AddWithValue("@" + i.ToString(), m);
                                }
                            }
                        }

                    }
                } catch {
                } if (command.EndsWith("where")) {
                    command = command.Remove(command.Length - 5, 5);
                } else {
                    command = command.Remove(command.Length - 4, 4);
                }
                cmd.CommandText = command;
                cmd.Connection = cnn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dtble);
                return dtble;
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu   :" + ex.Message);
            }
        }
        /// <summary>
        ///Tipi verilen class a ait tüm verileri getirir
        /// </summary>
        /// <param name="ObjectClassType">Class Type</param>
        public static DataTable GetObjects(Type ObjectClassType) {
            try {
                DataTable dt = new DataTable();
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                string command = "select * from  ";
                string[] tmp = ObjectClassType.ToString().Split('.');
                command += tmp[tmp.Length - 1];
                cmd.Connection = cnn;
                cmd.CommandText = command;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                return dt;
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  " + ex.Message, null);
            }
        }
        /// <summary>
        /// Parametre olarak verilen değerlerin içinde benzer değerler bulunan verileri datatable nesnesi döndürür
        /// </summary>
        /// <param name="myObjectClass">Class</param>
        /// <returns>DataTable</returns>
        public static DataTable IsLikeObjects(object myObjectClass) {
            try {
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                string command = "select ";
                DataTable dtble = new DataTable();
                try {
                    PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
                    object[] dizi = new string[pr.Length];
                    int i = 0;
                    foreach (PropertyInfo p in pr) {

                        object tt = myObjectClass.GetType().GetProperty(p.Name).PropertyType.FullName;
                        if (CheckType(tt.ToString())) {
                            dizi[i] = p.Name;
                            command += p.Name + ", ";
                            i++;
                        }
                    }
                    i = 0;
                    command = command.Remove(command.Length - 2, 1);
                    string[] tmp = myObjectClass.ToString().Split('.');
                    command += " from " + tmp[tmp.Length - 1] + " where";
                    foreach (string s in dizi) {
                        if (s != null) {
                            object m = myObjectClass.GetType().GetProperty(s).GetValue(myObjectClass, null);
                            if (m != null) {

                                object tt = myObjectClass.GetType().GetProperty(s).PropertyType.FullName;
                                if (CheckType(tt.ToString())) {
                                    i++;
                                    command += " " + s + " like '%" + m + "%' or ";
                                }
                            }
                        }

                    }
                } catch {
                } if (command.EndsWith("where")) {
                    command = command.Remove(command.Length - 5, 5);
                } else {
                    command = command.Remove(command.Length - 3, 3);
                }
                cmd.CommandText = command;
                cmd.Connection = cnn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dtble);
                return dtble;
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message, null);
            }
        }
        /// <summary>
        /// Parametre olarak verilen değerlerin içinde benzer değerler bulunan verileri gönderilen parametre tipinde bir generic list döndürür.
        /// </summary>
        /// <param name="myObjectClass">class</param>
        /// <returns>Generic List</returns>
        public static List<object> IsLikeObjectsList(object myObjectClass) {
            List<object> MainList = new List<object>();
            try {
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                SqlCommand cmd = new SqlCommand();
                string command = "select ";
                DataTable dtble = new DataTable();
                try {
                    PropertyInfo[] pr = myObjectClass.GetType().GetProperties();
                    object[] dizi = new string[pr.Length];
                    int i = 0;
                    foreach (PropertyInfo p in pr) {
                        object tt = myObjectClass.GetType().GetProperty(p.Name).PropertyType.FullName;
                        if (CheckType(tt.ToString())) {
                            dizi[i] = p.Name;
                            command += p.Name + ", ";
                            i++;
                        }
                    }
                    i = 0;
                    command = command.Remove(command.Length - 2, 1);
                    string[] tmp = myObjectClass.ToString().Split('.');
                    command += " from " + tmp[tmp.Length - 1] + " where";
                    foreach (string s in dizi) {
                        if (s != null) {
                            object m = myObjectClass.GetType().GetProperty(s).GetValue(myObjectClass, null);
                            if (m != null) {

                                object tt = myObjectClass.GetType().GetProperty(s).PropertyType.FullName;
                                if (CheckType(tt.ToString())) {
                                    i++;
                                    command += " " + s + " like '%" + m + "%' or ";
                                }
                            }
                        }

                    }
                } catch {
                } if (command.EndsWith("where")) {
                    command = command.Remove(command.Length - 5, 5);
                } else {
                    command = command.Remove(command.Length - 3, 3);
                }
                cmd.CommandText = command;
                cmd.Connection = cnn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dtble);
                foreach (DataRow dr in dtble.Rows) {
                    object bb = Activator.CreateInstance(myObjectClass.GetType(), true);
                    foreach (DataColumn dc in dtble.Columns) {
                        object d = dr[dc];
                        bool ss = dr[dc].Equals(DBNull.Value);
                        if (ss) {
                            d = null;
                        }
                        bb.GetType().GetProperty(dc.ColumnName).SetValue(bb, d, null);
                    }
                    MainList.Add(bb);
                }
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message, null);
            }
            return MainList;
        }
        /// <summary>
        /// Geri dönüş gerektirmeyen T-Sql sorguları içindir
        /// </summary>
        /// <param name="SqlString">sqlString</param>
        public static void ExecuteSql(string SqlString) {
            try {
                SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
                try {
                    SqlCommand cmd = new SqlCommand(SqlString, cnn);
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                    cnn.Close();
                } catch {
                    cnn.Close();
                }
            } catch (Exception ex) {

                throw new Exception("Hata Oluştu  :" + ex.Message);
            }
        }
        /// <summary>
        /// DataBase de oluşturulmuş geri dönüş gerektirmeyen Stored Procedure ler içindir
        /// </summary>
        /// <param name="ProcName">sample: sp_News</param>
        public static void ExecuteProc(string ProcName) {
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            try {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = ProcName;
                cnn.Open();
                cmd.ExecuteNonQuery();
                cnn.Close();
            } catch (Exception ex) {
                cnn.Close();
                throw new Exception("Hata Oluştu :" + ex.Message, null);
            }

        }
        private static void CreateProcedures(Type types, string spName) {
            CreateInsertProcedure(types, spName);
            CreateUpdateProcedure(types, spName);
            CreateDeleteProcedure(spName);
        }
        internal enum JoinType { Inner_Join, Left_Join, Right_Join, Full_Join, Outer_Join, Join }
        public  enum DevelopmentType { On,Off}

        private static void CreateInsertProcedure(Type types, string spName) {
            StringBuilder sb = new StringBuilder();
            sb.Append("Create Proc sp_" + spName + "_Insert  ");
            sb.AppendLine();
            foreach (PropertyInfo pr in types.GetProperties()) {
                if (pr.Name == "ID") {
                    continue;
                } else {
                    sb.Append("@" + pr.Name + "  " + GetDbType(types.GetProperty(pr.Name).PropertyType.FullName) + ", ");
                }
            }
            sb.Remove(sb.ToString().Length - 2, 2);
            string[] tmp = types.ToString().Split('.');
            sb.AppendLine();
            sb.Append("as");
            sb.AppendLine();
            sb.Append("insert into " + tmp[tmp.Length - 1] + " (");
            foreach (PropertyInfo pr in types.GetProperties()) {
                if (pr.Name == "ID") {
                    continue;
                } else {
                    sb.Append(pr.Name + ",");
                }
            }
            sb.Remove(sb.ToString().Length - 1, 1);
            sb.Append(") values (");
            foreach (PropertyInfo pr in types.GetProperties()) {
                if (pr.Name == "ID") {
                    continue;
                } else {
                    sb.Append("@" + pr.Name + " ,");
                }
            }
            sb.Remove(sb.ToString().Length - 1, 1);
            sb.Append(") ");
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            SqlCommand cmd = new SqlCommand(sb.ToString(), cnn);
            cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();

        }
        private static void CreateUpdateProcedure(Type types, string spName) {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("Create Proc sp_" + spName + "_Update ");
            sb.AppendLine();
            foreach (PropertyInfo pr in types.GetProperties()) {
                if (pr.Name == "ID") {
                    sb.Append("@ID  int,");
                    continue;
                } else {
                    sb.Append("@" + pr.Name + "  " + GetDbType(types.GetProperty(pr.Name).PropertyType.FullName) + ",");
                }
            }
            sb.Remove(sb.ToString().Length - 1, 1);
            sb.AppendLine();
            sb.Append("as");
            sb.AppendLine();
            string[] tmp = types.ToString().Split('.'); 
            foreach (PropertyInfo pr in types.GetProperties()) {
                if (pr.Name == "ID") {
                    continue;
                } else {
                    sb.Append("if(@" + pr.Name + " is not null)");
                    sb.AppendLine("update " + tmp[tmp.Length - 1] + " set "+pr.Name + "=@" + pr.Name + "  where ID=@ID ");
                }
            }  
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            SqlCommand cmd = new SqlCommand(sb.ToString(), cnn);
            cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();
        }
        private static void CreateDeleteProcedure(string spName) {
            StringBuilder sb = new StringBuilder();
            sb.Append("create proc sp_" + spName + "_delete");
            sb.AppendLine();
            sb.Append("@ID int");
            sb.AppendLine();
            sb.Append("as");
            sb.AppendLine();
            sb.Append("delete from " + spName + " where ID=@ID ");
            SqlConnection cnn = new SqlConnection(Tools.GetConnectionString);
            SqlCommand cmd = new SqlCommand(sb.ToString(), cnn);
            cnn.Open(); cmd.ExecuteNonQuery(); cnn.Close();
        }

    }
}
